package com.cn.ccp.service.course;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.CourseBean;
import com.cn.ccp.bean.CourseForumBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.service.course.CourseService.CourseRowMapper;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.Contants;

public class CourseForumService extends BasicService{
	
	private Log log = LogFactory.getLog(CourseForumService.class);
	
	
	public int getCount(String courseid){
		String sql = "SELECT count(1) FROM course_forum WHERE TYPE = 1 and status = 1 and course_id = ?	";
		return jdbcTemplate.queryForInt(sql ,new Object[]{courseid});
	}
	
	public int getManagerCount(String courseId,String title){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("SELECT count(1) FROM course_forum WHERE TYPE = 1 ");
		List<String> param = new ArrayList<String>();
		if(null != courseId && !"".equalsIgnoreCase(courseId.trim())){
			sqlBuf.append(" and course_id = ? ");
			param.add(courseId);
		}
		if(null != title && !"".equalsIgnoreCase(title.trim())){
			sqlBuf.append(" and title like ? ");
			param.add("%"+title+"%");
		}
		if(param.size() == 0)	
			return jdbcTemplate.queryForInt(sqlBuf.toString() );
		else
			return jdbcTemplate.queryForInt(sqlBuf.toString() ,param.toArray());
	}
	
	
	public int getReplyCountManager(String id){
		String sql = "SELECT count(1) FROM course_forum WHERE TYPE = 2 and reply_id = ?" ;
		return jdbcTemplate.queryForInt(sql , new Object[]{id});
	}
	
	public int getReplyCount(String id){
		String sql = "SELECT count(1) FROM course_forum WHERE TYPE = 2 and status = 1 and reply_id = ?" ;
		return jdbcTemplate.queryForInt(sql , new Object[]{id});
	}
	
	
	public boolean update(String courseId ,String title ,String contents ,String id){
		String sql = "update course_forum set course_id = ?, title = ? ,contents = ?, update_time = CURRENT_TIMESTAMP where id = ? ";
		return jdbcTemplate.update(sql, new Object[]{courseId ,title ,contents,id})>0 ;
	}
	
	public boolean update(String title ,String contents ,String id){
		String sql = "update course_forum set  title = ? ,contents = ?, update_time = CURRENT_TIMESTAMP where id = ? ";
		return jdbcTemplate.update(sql, new Object[]{title ,contents,id})>0 ;
	}
	
	
	public boolean install(String courseId ,String title ,String contents ,String uid , int type){
		return install( courseId,title , contents , uid ,  type ,"0");
	}
	
	public boolean install(String courseId ,String title ,String contents ,String uid , int type ,String repyid){
		String sql = "INSERT INTO course_forum (course_id,title,contents,user_id,create_time,type,see_times,reply_times,reply_id,update_time)VALUES(?,?,?,?,CURRENT_TIMESTAMP,?,0,0,?,CURRENT_TIMESTAMP)";
		return jdbcTemplate.update(sql, new Object[]{courseId,title ,contents,uid, type,repyid})>0 ;
	}
	
	public void look(String id){
		String sql = "UPDATE course_forum SET see_times = see_times + 1 WHERE id = ?";
		jdbcTemplate.update(sql, new Object[]{id});
	}
	public void reply(String id){
		String sql = "UPDATE course_forum SET reply_times = reply_times + 1 , update_time = CURRENT_TIMESTAMP WHERE id = ?";
		jdbcTemplate.update(sql, new Object[]{id});
	}
	
	/**
	 * 删除主题
	 * @param id
	 */
	public void deleteMainForum(String id){
		String sql = "delete form course_forum where reply_id = ? or id = ? ";
		jdbcTemplate.update(sql, new Object[]{id,id});
	}
	
	/**
	 * 删除回复内容
	 * @param id
	 */
	public void deleteReplyForum(String id){
		String sql = "delete from course_forum where id = ? ";
		jdbcTemplate.update(sql, new Object[]{id});
	}
	
	
	public CourseForumBean showBean(String id){
		String sql = "SELECT cf.* ,u.username, cf.course_id ,c.courses_name  FROM course_forum cf "+
		" LEFT JOIN USER u ON u.id = cf.user_id"+
		" left join courses c on c.id = cf.course_id"+
		" WHERE cf.id = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new Object[]{id}, new CourseForumMapper());
		} catch (Exception e) {
			return new CourseForumBean();
		}
	}
	
	
	public void disableContent(String id){
		String sql = "UPDATE course_forum set status =? WHERE id	 = ?;";
		jdbcTemplate.update(sql,new Object[]{Contants.DESABLE_FORUM,id});
	}
	
	public void enableContent(String id){
		String sql = "UPDATE course_forum set status = ? WHERE id	 = ?;";
		jdbcTemplate.update(sql,new Object[]{Contants.ENABLE_FORUM,id});
	}
	
	
	public void deleteContent(String id){
		String sql = "DELETE FROM course_forum WHERE reply_id	 = ? ;";
		jdbcTemplate.update(sql,new Object[]{id});
		sql = "DELETE FROM course_forum WHERE id	 = ? ;";
		jdbcTemplate.update(sql,new Object[]{id});
	}
	
	
	public List<CourseForumBean> pageQueryManager(int start, int pagesize,String courseId,String lookTitle) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("SELECT cf.* ,u.username ,  cf.course_id ,c.courses_name ,c.teacher_id FROM course_forum cf ");
		sqlBuf.append(" LEFT JOIN USER u ON u.id = cf.user_id");
		sqlBuf.append(" left join courses c on c.id = cf.course_id ");
		sqlBuf.append(" WHERE cf.type = 1");
		List<Object> param = new ArrayList<Object>();
		
		if(null != courseId && !"".equalsIgnoreCase(courseId.trim())){
			sqlBuf.append(" and cf.course_id = ? ");
			param.add(courseId);
		}
		if(null != lookTitle && !"".equalsIgnoreCase(lookTitle.trim())){
			sqlBuf.append(" and title like ? ");
			param.add("%"+lookTitle+"%");
		}
		param.add(start);
		param.add(pagesize);
		sqlBuf.append(" ORDER BY cf.create_time  DESC  limit ?,?");
		return jdbcTemplate.query(sqlBuf.toString(), param.toArray(),new CourseForumMapper(null));
	}
	
	public List<CourseForumBean> pageQuery(int start, int pagesize ,String teacher , String courseID) {
		String sql = "SELECT cf.* ,u.username ,  cf.course_id ,c.courses_name ,c.teacher_id FROM course_forum cf "+
					" LEFT JOIN USER u ON u.id = cf.user_id"+
					" left join courses c on c.id = cf.course_id"+
					" WHERE cf.type = 1 and cf.status = 1 and cf.course_id = ? ORDER BY cf.create_time  DESC   limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { courseID ,start,pagesize},new CourseForumMapper(teacher));
	}
	
	public List<CourseForumBean> replyPageQueryManager(int start, int pagesize , String id) {
		String sql = "SELECT cf.* ,u.username, cf.course_id ,c.courses_name FROM course_forum cf "+
					" LEFT JOIN USER u ON u.id = cf.user_id"+
					" left join courses c on c.id = cf.course_id"+
					" WHERE cf.type = 2 and cf.reply_id = ?  limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { id, start,pagesize},new CourseForumMapper());
	}
	
	
	public List<CourseForumBean> replyPageQuery(int start, int pagesize , String id) {
		String sql = "SELECT cf.* ,u.username, cf.course_id ,c.courses_name FROM course_forum cf "+
					" LEFT JOIN USER u ON u.id = cf.user_id"+
					" left join courses c on c.id = cf.course_id"+
					" WHERE cf.type = 2 and cf.status = 1 and cf.reply_id = ?  limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { id, start,pagesize},new CourseForumMapper());
	}
	
	
	
	public List<CourseBean> getAllCourse(){
		String sql = "SELECT id,courses_name  FROM courses   ";
		return jdbcTemplate.query(sql, new CourseRowMapper());
	}
	
	
	private class CourseRowMapper implements RowMapper{
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			CourseBean b = new CourseBean();
			b.setId(rs.getString("id"));
			b.setCourse_name(rs.getString("courses_name"));
			return b;
		}
		
	}
	
	
	public class CourseForumMapper implements RowMapper {
		
		private String longUser ;
		public CourseForumMapper(){}

		
		public CourseForumMapper(String longUser){
			this.longUser = longUser ;
		}
		
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseForumBean bean = new CourseForumBean();
			bean.setId(rs.getInt("id"));
			bean.setTitle(rs.getString("title"));
			bean.setContents(rs.getString("contents"));
			bean.setUserName(rs.getString("username"));
			bean.setUserId(rs.getString("user_id"));
			bean.setCreateTime(rs.getString("create_time").substring(0, 19));
			bean.setSeeTimes(rs.getInt("see_times"));
			bean.setReplyTimes(rs.getInt("reply_times"));
			bean.setReplyId(rs.getInt("reply_id"));
			bean.setUpdateTime(rs.getString("update_time"));
			bean.setCourseId(rs.getString("course_id"));
			bean.setCourseName(rs.getString("courses_name"));
			try{
			if(bean.getUserId().equals(CCPContants.getCurrUserId()))
				bean.setShowEdit(true);
			}catch(Exception e){}
			try {
				if(!bean.getUpdateTime().equals(""))
					bean.setUpdateTime(bean.getUpdateTime().substring(0, 10));
			} catch (Exception e) {
			}
			try {
				bean.setTeacherId(rs.getString("teacher_id"));
				bean.setShowDelete(longUser.equals(bean.getTeacherId()));
			} catch (Exception e) {
			}
			bean.setStatus(rs.getInt("status"));
			try{
				bean.setStatusName(bean.getStatus() == 1 ? BaseAction.getInstance().getMessages("user_page_label_status_ok"):BaseAction.getInstance().getMessages("btn_pingbi"));
			}catch(Exception e){};
			return bean;
		}
	}

}
